package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.AreaEntity;
import com.css.fxfzypg.modules.baseData.queryParams.AreaQueryParams;
import com.css.fxfzypg.modules.baseData.repository.AreaRepository;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-10 14:20
 */
@Repository
public class AreaRepositoryImpl implements AreaRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public AreaEntity updateArea(AreaEntity areaEntity) {

        String sql = "";
            sql = "update data_area set street = :street, village = :village, is_delete =:isDelete where area_id = '"+ areaEntity.getAreaId() +"'";
            SqlParameterSource source = new BeanPropertySqlParameterSource(areaEntity);
            //拿到新插入的主键值（i）
            KeyHolder keyHolder = new GeneratedKeyHolder();
            int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
            return areaEntity;
    }

    @Override
    public Page<AreaEntity> findArea(AreaQueryParams areaQueryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_area u ");

      //  StringBuilder orderSql = new StringBuilder(" order by u.create_time");

        StringBuilder whereSql = new StringBuilder(" where u.is_delete = '" + YNEnum.N.toString() + "'");
        String wholeSql = sql.append(whereSql).toString();
        List<AreaEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(AreaEntity.class));
        Pageable pageable = PageRequest.of(areaQueryParams.getCurPage() - 1, areaQueryParams.getPageSize());
        Page<AreaEntity> areaEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return areaEntities;
    }


    @Override
    public void deleteAreaById(String areaId) {
        String sql="";
        sql="update data_area  set  is_delete= '"+ YNEnum.Y.toString()+"' where area_id='"+areaId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void addArea(AreaEntity areaEntity) {
        //拿到新插入的主键值（i）
        String sql="insert into data_area values (:area_id, :street, :village, :is_delete) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(areaEntity);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);

    }

    @Override
    public AreaEntity findByAreaid(String areaId) {
        String sql="";
        sql="select u.* from data_area u  where u.area_id='"+areaId+"'";
        AreaEntity areaEntity = jdbcTemplate.queryForObject(sql, AreaEntity.class);
        return areaEntity;
    }

    @Override
    public void deleteManyArea(List<String> AreaIds) {
        String sql = "update data_area set is_delete = '" + YNEnum.Y + "' where area_id in (:AreaIds)";
        Map map = new HashMap();
        map.put("AreaIds", AreaIds);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<AreaEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_area u where u.is_delete='"+ YNEnum.N.toString()+"'",new BeanPropertyRowMapper<>(AreaEntity.class));
    }

    @Override
    public void importExcel(List<AreaEntity> areaEntities) {
        jdbcTemplate.batchUpdate("insert into data_area (areaid,street,village,isdelete)values(?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,areaEntities.get(i).getAreaId());
                ps.setString(2,areaEntities.get(i).getStreet());
                ps.setString(3,areaEntities.get(i).getVillage());
                ps.setString(4,areaEntities.get(i).getIsDelete());

            }

            @Override
            public int getBatchSize() {
                return areaEntities.size();
            }
        });
    }

    @Override
    public List<AreaEntity> getStreet(String province,String city,String county) {
        return jdbcTemplate.query("select u.street,st_astext(u.geom) as geomText from data_area u where u.is_delete='"+ YNEnum.N.toString()+"' and province='"+province+"' and city='"+city+"' and county='"+county+"'",new BeanPropertyRowMapper<>(AreaEntity.class));
    }

}
